package com.huayue.dao;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Component;

import com.huayue.entity.Reserve;
import com.huayue.entity.RoomType;
import com.huayue.util.DateHelper;
import com.huayue.util.PageHelper;
import com.mysql.DBHelper;

/**
 * 房间类型
 */
@Component
public class RoomTypeDao implements BaseDao<RoomType> {
    @Override
    public int insert(RoomType entity) {
        String sql =
            "insert into room_type (name, price, floors, size, iswindow, iswifi, isweb, peoplenumber, bednumber, is_noroom,  is_deleted) values (?, ?, ?, ?, ?, ?, ?, ?, ?, 1, 0)";
        return DBHelper.executeUpdate(sql, entity.getName(), entity.getPrice(), entity.getFloors(), entity.getSize(),
            entity.getIswindow(), entity.getIswifi(), entity.getIsweb(), entity.getPeoplenumber(),
            entity.getBednumber());
    }

    @Override
    public int update(RoomType entity) {
        String sql =
            "update room_type set name=?,price=?, floors=?, size=?,peoplenumber=?, bednumber=?,iswindow=?,iswifi=?,isweb=? where id=?";
        return DBHelper.executeUpdate(sql, entity.getName(), entity.getPrice(), entity.getFloors(), entity.getSize(),
            entity.getPeoplenumber(), entity.getBednumber(), entity.getIswindow(), entity.getIswifi(),
            entity.getIsweb(), entity.getId());
    }

    @Override
    public int delete(RoomType entity) {
        String sql = "update room_type set is_deleted=1 where id=?";
        return DBHelper.executeUpdate(sql, entity.getId());
    }

    @Override
    public List<Map<String, Object>> findAll(Object... obj) {
        return DBHelper.executeQuery("select * from room_type where is_deleted=0");
    }

    @Override
    public List<Map<String, Object>> findPage(PageHelper page, Object... obj) {
        RoomType room_type = (RoomType)obj[0];
        if ("0".equals(room_type.getPrice_max())) {
            room_type.setPrice_max("1000000000000000000000");
        }
        if ("0".equals(room_type.getPrice_min())) {
            room_type.setPrice_min("0.1");
        }
        String sql = "select * from room_type rt where 1=1 and rt.is_deleted=0 ";
        // if (room_type.getRoom_product_type_id() != -1 && !"".equals(room_type.getRoom_product_type_id())) {
        // sql += " and rt.room_product_type_id = " + room_type.getRoom_product_type_id() + " ";
        // }
        if (room_type.getPeoplenumber() != 0 && !"".equals(room_type.getPeoplenumber())) {
            sql += " and rt.peoplenumber = " + room_type.getPeoplenumber() + "";
        }
        if (!"0".equals(room_type.getPrice_min()) && !"0".equals(room_type.getPrice_max())) {
            sql += " and rt.price BETWEEN '" + room_type.getPrice_min() + "' AND '" + room_type.getPrice_max() + "'";
        }
        return DBHelper.executeQuery(sql + " ORDER BY rt.id asc  limit ?,?  ", page.getBegin(), page.getPageSize());
    }

    @Override
    public Map<String, Object> findOne(RoomType entity) {
        String sql = "select * from room_type rt where rt.id=? and rt.is_deleted=0";
        return DBHelper.executeQuery(sql, entity.getId()).get(0);
    }

    @Override
    public int count(Object... obj) {
        RoomType room_type = (RoomType)obj[0];
        if ("0".equals(room_type.getPrice_max())) {
            room_type.setPrice_max("1000000000000000000000");
        }
        if ("0".equals(room_type.getPrice_min())) {
            room_type.setPrice_min("0.1");
        }
        String sql = " select count(*) counts from room_type rt where 1=1 and rt.is_deleted=0 ";
        // if (room_type.getRoom_product_type_id() != -1 && !"".equals(room_type.getRoom_product_type_id())) {
        // sql += " and rt.room_product_type_id =" + room_type.getRoom_product_type_id() + "";
        // }
        if (room_type.getPeoplenumber() != 0 && !"".equals(room_type.getPeoplenumber())) {
            sql += " and rt.peoplenumber = " + room_type.getPeoplenumber() + "";
        }

        if (!"0".equals(room_type.getPrice_min()) && !"0".equals(room_type.getPrice_max())) {
            sql += " and rt.price BETWEEN '" + room_type.getPrice_min() + "' AND '" + room_type.getPrice_max() + "'";
        }
        return Integer.valueOf(DBHelper.executeQuery(sql).get(0).get("counts").toString());
    }

    // 外键 查询Room_product_type表
    public List<Map<String, Object>> findRoom_product_type() {
        String sql = "select * from room_product_type where is_deleted=0";
        return DBHelper.executeQuery(sql);
    }

    /**
     * 修改Logo
     *
     * @param entity
     * @return
     */
    public int updateIcon(RoomType entity) {
        return DBHelper.executeUpdate("update room_type set icon=? where id=?", entity.getIcon(), entity.getId());
    }

    public List<Map<String, Object>> findAvailableAll() {
        String sql = "select rt.id,rt.name,rt.price from room_status rs INNER JOIN room r on rs.room_id=r.id \n"
            + "INNER JOIN room_type rt on r.room_type_id=rt.id \n"
            + "INNER JOIN room_status_type rst on rs.status_id=rst.id where rst.id=1 group BY rt.id";
        return DBHelper.executeQuery(sql);
    }

    /**
     * 查询客户条件下的房间信息 类型 根据入住时间，离开时间
     * 
     * @param reserve
     * @return
     */
    public List<Map<String, Object>> findAvailRoom(Reserve reserve, Integer roomTypeId) {
        // 若无则使用默认值
        if (reserve.getInDate() == null || "".equals(reserve.getInDate())) {
            reserve.setInDate(DateHelper.DateToStringShort(new Date()));
            Calendar c = Calendar.getInstance();
            c.setTime(new Date());
            c.add(Calendar.DAY_OF_MONTH, 1);
            reserve.setOutDate(DateHelper.DateToStringShort(c.getTime()));
        }
        if ((reserve.getMoneyMin() == null || reserve.getMoneyMax() == null) && roomTypeId == 0) {
            reserve.setMoneyMin(new BigDecimal("50"));
            reserve.setMoneyMax(new BigDecimal("500"));
        }
        String sql = "select rt.*,count(r.id) counts from room_type rt \n"
            + " left join room r on rt.id=r.room_type_id  left join room_status rs on rs.room_id=r.id\n"
            + " where 1=1  and rt.is_deleted=0 and (rs.status_id=1 or (rs.in_time < ? and rs.out_time < ?))\n"
            + " and r.is_deleted=0 ";
        if (reserve.getPeopleNumber() != 0) {
            sql += " and rt.peoplenumber>= " + reserve.getPeopleNumber();
        }
        if (reserve.getMoneyMin() != null && reserve.getMoneyMax() != null) {
            sql += " and rt.price BETWEEN " + reserve.getMoneyMin() + " and " + reserve.getMoneyMax();
        }
        if (roomTypeId != 0) {
            sql += " and rt.id= " + roomTypeId;
        }
        sql += " group by rt.id ";
        return DBHelper.executeQuery(sql, reserve.getOutDate(), reserve.getInDate());
    }

    /**
     * 查询每种房间类型在指定时间内的总收入
     *
     * @return
     */
    public List<Map<String, Object>> findTotalPrice(PageHelper pageHelper, RoomType roomType, String begin,
        String end) {
        String sql = "select rt.id,rt.name,rt.floors,rt.size,IFNULL(d.tprice,0) price from room_type rt\n"
            + "left join  (select rt.id,sum(rrod.price) tprice from room_type rt\n"
            + "left join room_real_order_details rrod on rrod.room_type_id=rt.id where rrod.days != 0 ";
        if (begin != null && end != null) {
            sql += " and rrod.gmt_modified between '" + begin + "' and '" + end + "' ";
        }
        sql += "group by rt.id)d on d.id= rt.id\n";
        if (roomType.getName() != null && !"".equals(roomType.getName())) {
            sql += "where rt.name like '%" + roomType.getName() + "%'";
        }
        sql += " order by price desc ";
        sql += " limit ?,? ";
        return DBHelper.executeQuery(sql, pageHelper.getBegin(), pageHelper.getPageSize());
    }

    /**
     * 查询每种房间类型在指定时间内的总收入
     *
     * @return
     */
    public int findTotalPriceCount(RoomType roomType, String begin, String end) {
        String sql = "select count(*) counts from room_type rt\n"
            + "left join  (select rt.id,sum(rrod.price) tprice from room_type rt\n"
            + "left join room_real_order_details rrod on rrod.room_type_id=rt.id where rrod.days != 0 ";
        if (begin != null && end != null) {
            sql += " and rrod.gmt_modified between '" + begin + "' and '" + end + "' ";
        }
        sql += "group by rt.id)d on d.id= rt.id\n";
        if (roomType.getName() != null && !"".equals(roomType.getName())) {
            sql += "where rt.name like '%" + roomType.getName() + "%'";
        }
        return Integer.valueOf(DBHelper.executeQuery(sql).get(0).get("counts").toString());
    }

}
